//select.js
const express = require('express');
const http = require('http');
const app = express()
var router = express.Router();
const connection = require('./mysql/connection'); //导入mysq配置文件

//创建一个connection连接
connection.connect(function (err) {
    if (err) {
        console.log('[query] - :' + err);
        return;
    }
    console.log('[connection connect]  succeed!'); //如果连接成功 控制台输出 success 了
});


app.get('/dbList', function (req, res) {
    var res = res;
    var req = req;

    var sql = `SELECT
    schema_name
    FROM information_schema.schemata
    WHERE 
    schema_name NOT IN ('mysql', 'information_schema', 'test', 'search', 'tbsearch', 'sbtest', 'dev_ddl', 'performance_schema')`;

    connection.query(sql, function (err, rows, fields) {
        if (err) {
            console.log('[query-err] :' + err);
            return;
        }
        console.log(rows)
        res.send(rows) //这里在页面上输出数据
    });
})


app.get('/tableList', function (req, res) {
    var res = res;
    var req = req;

    var is_simple = req.query.is_simple || true;


    //执行SQL语句,这里是一条简单的MySQL查询语句
    var sql = `SELECT
                    table_schema table_schema,
                    table_name table_name,
                    table_rows table_rows,
                    table_comment table_comment 
                FROM
                    information_schema.TABLES 
                WHERE
                    table_schema = '${req.query.table_schema}'`;
    connection.query(sql, function (err, rows, fields) {
        if (err) {
            console.log('[query-err] :' + err);
            return;
        }
        console.log(rows)

        res.send(rows)
    });
})

app.get('/tableColumList', function (req, res) {
    var res = res;
    var req = req;


    //执行SQL语句,这里是一条简单的MySQL查询语句
    var sql = `SELECT
                    lower(column_name) column_name,
                    ordinal_position ordinal_position,
                    column_default column_default,
                    substring(is_nullable, 1, 1) is_nullable,
                    column_type column_type,
                    column_comment column_comment,
                    character_maximum_length character_maximum_length,
                    numeric_precision numeric_precision,
                    numeric_scale numeric_scale
                FROM
                    information_schema. COLUMNS
                WHERE
                    table_schema = '${req.query.table_schema}' 
                AND table_name = '${req.query.table_name}';`;
    connection.query(sql, function (err, rows, fields) {
        if (err) {
            console.log('[query-err] :' + err);
            return;
        }
        console.log(rows)

        res.send(rows)
    });
})




module.exports = app